package leagal_aid.bean;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import leagal_aid.util.DBUtil;



public class TeamBean {
	private int id;
	private String picture;
	private String content;
	private  Date time;
	private String category;
	private String name;
	private int puton;
	private int homepage;
	
	PreparedStatement statement=null;
	ResultSet rs = null;
	Connection connection = null;
	
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getPicture() {
		return picture;
	}
	public void setPicture(String picture) {
		this.picture = picture;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public Date getTime() {
		return time;
	}
	public void setTime(Date time) {
		this.time = time;
	}
	public String getCategory() {
		return category;
	}
	public void setCategory(String category) {
		this.category = category;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getPuton() {
		return puton;
	}
	public void setPuton(int puton) {
		this.puton = puton;
	}
	public int getHomepage() {
		return homepage;
	}
	public void setHomepage(int homepage) {
		this.homepage = homepage;
	}
	public List<TeamBean> findAll() {
		List<TeamBean> list = new ArrayList<TeamBean>();
		DBUtil dbUtil = new DBUtil();
		String sql = "select * from team order by time desc";
		connection = dbUtil.geConnection();
		try {
			statement = connection.prepareStatement(sql);
			rs = statement.executeQuery();
			while(rs.next()) {
				TeamBean teamBean = new TeamBean();
				teamBean.setId(rs.getInt("id"));
				teamBean.setPicture(rs.getString("picture"));
				teamBean.setContent(rs.getString("content"));
				teamBean.setTime(rs.getDate("time"));
				teamBean.setName(rs.getString("name"));
				teamBean.setHomepage(rs.getInt("homepage"));
				list.add(teamBean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return list;
		
	}
		//首页获取
	public List<TeamBean> findpicture() {
		List<TeamBean> list = new ArrayList<TeamBean>();
		DBUtil dbUtil = new DBUtil();
		String sql = "select * from team where homepage=1 order by time desc";
		connection = dbUtil.geConnection();
		try {
			statement = connection.prepareStatement(sql);
			rs = statement.executeQuery();
			while(rs.next()) {
				TeamBean teamBean = new TeamBean();
				teamBean.setId(rs.getInt("id"));
				teamBean.setPicture(rs.getString("picture"));
				teamBean.setContent(rs.getString("content"));
				teamBean.setTime(rs.getDate("time"));
				teamBean.setName(rs.getString("name"));
				teamBean.setHomepage(rs.getInt("homepage"));
				list.add(teamBean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return list;
		
	}
	
	public TeamBean findNewsById(int id) {
		System.out.println("run findNewsById : " + id);
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		TeamBean teamBean = new TeamBean();
		String sql = "select * from team where id = ?";
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			rs = statement.executeQuery();
			if(rs.next()) {
				
				teamBean.setContent(rs.getString("content"));
				teamBean.setPicture(rs.getString("picture"));
				teamBean.setTime(rs.getDate("time"));
				teamBean.setName(rs.getString("name"));
				teamBean.setHomepage(rs.getInt("homepage"));
				System.out.println(teamBean.getPicture());
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		System.out.println( "end findNewsById :");
		return teamBean;
		
	}
	
public boolean doAdd(String picture,String content,String time,String name,String category,int homepage) {
	boolean add = false;
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	//String sql = "select password from admin where username = " + username + "'";
	String sql = "insert into team (picture,content,time,name,category,homepage) values(?,?,?,?,?,?)";
	
  try {		
	  PreparedStatement statement = connection.prepareStatement(sql);
		statement.setString(1, picture);
		statement.setString(2, content);
		statement.setString(3, time);
		statement.setString(4, name);
		statement.setString(5, category);
		statement.setInt(6, homepage);
	    int i = statement.executeUpdate();
		if (i>0) {
			add = true;
		}
			
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return add;
	
}

public boolean doDel(int id) {
	boolean del = false;
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	//String sql = "select password from admin where username = " + username + "'";
	String sql = "delete from team where id =?";
	try {
		statement = connection.prepareStatement(sql);
		statement.setInt(1, id);
		int i = statement.executeUpdate();
		if (i>0) {
			del = true;
		}
			
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return del;
	
}

public boolean doUpdata(String content,String picture,String txtDate,String name,String category,Integer homepage,int id) {
	boolean updata = false;
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	//String sql = "select password from admin where username = " + username + "'";
	String sql = "update team set content =?,picture =?,time=?,name=?,category=?,homepage=? where id =?";
	try {
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setString(1, content);
		statement.setString(2, picture);
		statement.setString(3, txtDate);
		statement.setString(4, name);
		statement.setString(5, category);
		statement.setInt(6, homepage);
		statement.setInt(7, id);
		System.out.println(updata);
		int i = statement.executeUpdate();
		if (i>0) {
			updata = true;
		}
			
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return updata;
	
}
public List<TeamBean> findAllByLb() {
	List<TeamBean> list = new ArrayList<TeamBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from team ";
	try {
		statement = connection.prepareStatement(sql);
		rs = statement.executeQuery();
		while(rs.next()) {
			TeamBean teamBean = new TeamBean();
			teamBean.setCategory(rs.getString("category"));
			teamBean.setId(rs.getInt("id"));
			teamBean.setHomepage(rs.getInt("homepage"));
			teamBean.setContent(rs.getString("content"));
			teamBean.setTime(rs.getDate("time"));
			teamBean.setPicture(rs.getString("picture"));
			System.out.println(teamBean.getPicture());
			list.add(teamBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}



public List<TeamBean> findbackByAllForFY(Page page) {
	List<TeamBean> list = new ArrayList<TeamBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from team order by time desc limit ?,? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setInt(1, page.getBeginIndex());
		statement.setInt(2, page.getEveryPage());
		rs = statement.executeQuery();
		while(rs.next()) {
			TeamBean teamBean = new TeamBean();
			teamBean.setId(rs.getInt("id"));
			teamBean.setContent(rs.getString("content"));
			teamBean.setTime(rs.getDate("time"));
			teamBean.setPicture(rs.getString("picture"));
			teamBean.setHomepage(rs.getInt("homepage"));
			System.out.println(teamBean.getPicture());
			list.add(teamBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}

//分页查询
public int findbackCountByLb(String lbString) {
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	int count = 0;
	String sql = "select count(*) totalCount from team where category =? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		rs = statement.executeQuery();
		if (rs.next()) {
			count=rs.getInt("totalCount");
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return count;
	
}


//后端模糊查询后总数
public int findselectCountByLb(String lbString,String tilString) {
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	int count = 0;
	String sql = "select count(*) totalCount from team where category =? and concat(id,name,content,category,time,picture,homepage) like ? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		statement.setString(2,"%" + tilString + "%");
		rs = statement.executeQuery();
		if (rs.next()) {
			count=rs.getInt("totalCount");
			System.out.print("totalCount :"+count);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return count;
	
}

//后端分页
public List<TeamBean> findbackByLbForFY(String lbString,Page page) {
	List<TeamBean> list = new ArrayList<TeamBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from team where category =? order by time desc limit ?,? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		statement.setInt(2, page.getBeginIndex());
		statement.setInt(3, page.getEveryPage());
		rs = statement.executeQuery();
		while(rs.next()) {
			TeamBean teamBean = new TeamBean();
			teamBean.setId(rs.getInt("id"));
			teamBean.setCategory(rs.getString("category"));
			teamBean.setName(rs.getString("name"));
			teamBean.setContent(rs.getString("content"));
			teamBean.setTime(rs.getDate("time"));
			teamBean.setPicture(rs.getString("picture"));
			teamBean.setHomepage(rs.getInt("homepage"));
			System.out.println(teamBean.getPicture());
			list.add(teamBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}
//模糊查询
public List<TeamBean> Proselect(String lbString,String tilString,Page page) {
	    List<TeamBean> list =new ArrayList<TeamBean>();
	    DBUtil dbUtil = new DBUtil();
	    connection = dbUtil.geConnection();
	    //String sql = "select * from news";
	    String sql ="select * from team where category =? and concat(id,name,content,category,time,picture,homepage) like ? order by time desc limit ?,?" ;
	    connection = dbUtil.geConnection();
	    try {
	     statement = connection.prepareStatement(sql);
	     statement.setString(1, lbString);
	     statement.setString(2,"%" + tilString + "%");
	     statement.setInt(3, page.getBeginIndex());
	     statement.setInt(4, page.getEveryPage());
	     rs = statement.executeQuery();
	     while(rs.next()) {
	    	   TeamBean teamBean = new TeamBean();
			   teamBean.setId(rs.getInt("id"));
			   teamBean.setName(rs.getString("name"));
			   teamBean.setContent(rs.getString("content"));
			   teamBean.setCategory(rs.getString("category"));
			   teamBean.setTime(rs.getDate("time"));
			   teamBean.setPicture(rs.getString("picture"));
			   teamBean.setHomepage(rs.getInt("homepage"));
			   list.add(teamBean);
	     }
	     
	    } catch (SQLException e) {
	     e.printStackTrace();
	    }finally {
	     if (rs != null) {
	      try {
	       rs.close();
	      } catch (SQLException e) {
	       e.printStackTrace();
	      }
	     }
	     if (statement != null) {
	      try {
	       statement.close();
	      } catch (SQLException e) {
	       e.printStackTrace();
	      }
	     }
	     if(connection != null) {
	      try {
	       connection.close();
	      } catch (SQLException e) {
	       e.printStackTrace();
	      }
	     }
	     dbUtil.close();
	    }
	    return list;
	    
	   }




}

